/**
 *
 * @(#) TestDemo.java
 * @Package jdbc
 * 
 * Copyright © *** Corporation. All rights reserved.
 *
 */

package com.chen.jdbc;

import java.io.BufferedOutputStream;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

/**
 *  类描述：数据库表跟java对象操作之间的转换
 * 
 *  @author:  Administrator
 *  @version  $Id: Exp$ 
 *           
 */
public class Tool {
	public static final String[] DRIVER_NAME = {"com.mysql.jdbc.Driver"};
	public static final String[] URL = {
						"jdbc:mysql://localhost:3306/wanyicore",
					};
	public static final String[] USER = {
						"root",
					};
	public static final String[] PASSWORD = {
						"root",
					};
	public static final String[] TABLE_NAME={
						"tenant_weixin_info"
					};
	public static final String PATH = "D:/filetest/";
	public static final String SUFFIX = ".txt";//文件后缀名
	public static final String ENCODING = "UTF-8";
	public static final Boolean FLAG = true;
	/**
	 * 0-mysql
	 */
	public static final int database_flag=0;
	public static final int url_flag=0;
	public static final int tableName_flag=0;
	/**
	 * 
	 */
	public static final int key=1;
	
	/**
	 * 1:<result column="" property="" />
	 * 2:#{id}
	 * 3:private String id;
	 * 4:ID = #{id}
	 * ...
	 */
	public static final int NUM = 3;
	

	public static void main(String[] args) {
		switch (key) {
		case 1:
			insert();
			System.out.println("*******************************************************");
			update();
			System.out.println("*******************************************************");
			select();
			System.out.println("*******************************************************");
			getEntity();
			break;
		case 2:
			conver("communityCampaignInfoVo","communityCampaignInfo",false);
			break;
		case 3:
			converToNewString(converToField(TABLE_NAME[tableName_flag]));
			break;
		case 4:
			getEntity();
			break;
		default:
			break;
		}
		//doSigleTable(TABLE_NAME[tableName_flag]);
		/*doSigleTable(TABLE_NAME[tableName_flag]);*/
		/*doSigleTable(TABLE_NAME[tableName_flag]);
		doAllTables();
		String filepath=PATH+TABLE_NAME[tableName_flag]+SUFFIX;
		readTxtFile(filepath);
		System.out.println("执行完毕！");*/
	}
	
	private static void insert(){
		Connection con = getConnection();
		ResultSet rs = null;
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			rs = dbmd.getColumns(null, "%", TABLE_NAME[tableName_flag], "%");
			StringBuffer sb = new StringBuffer();
			StringBuffer sbValues = new StringBuffer();
			sb.append("INSERT INTO "+TABLE_NAME[tableName_flag]+"\r\n"+"(");
			while(rs.next()){
				sb.append(rs.getString("COLUMN_NAME")+",");
				sbValues.append(/*format2(rs.getString("COLUMN_NAME").toUpperCase())+*/"?,");
			}
			sb.deleteCharAt(sb.length()-1);
			sbValues.deleteCharAt(sbValues.length()-1);
			sb.append(") \r\n VALUES \r\n(");
			sb.append(sbValues);
			sb.append(")");
			System.out.println(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				con.close();
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static void update(){
		Connection con = getConnection();
		ResultSet rs = null;
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			rs = dbmd.getColumns(null, "%", TABLE_NAME[tableName_flag], "%");
			StringBuffer sb = new StringBuffer();
			sb.append("UPDATE "+TABLE_NAME[tableName_flag]+"\r\n SET ");
			while(rs.next()){
				String columnName = rs.getString("COLUMN_NAME").toUpperCase();
				//sb.append(columnName+" = " +format2(columnName)+",");
				sb.append(columnName+" = ?,");
			}
			sb.deleteCharAt(sb.length()-1);
			System.out.println(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				con.close();
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static void select(){
		Connection con = getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from "+TABLE_NAME[tableName_flag]);
			ResultSetMetaData meta = rs.getMetaData();
			StringBuffer sb = new StringBuffer();
			sb.append("SELECT ");
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				String columnName = meta.getColumnName(i).toUpperCase();
				sb.append(columnName+",");
			}
			sb.deleteCharAt(sb.length()-1);
			sb.append(" FROM "+TABLE_NAME[tableName_flag]);
			System.out.println(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				rs.close();
				con.close();
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static void getEntity(){
		Connection con = getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			/*rs = dbmd.getTables(null, "%", "%", new String[]{"TABLE"});
			while(rs.next()){
				String tableName = rs.getString("TABLE_NAME");
				if(tableName.equals(TABLE_NAME[tableName_flag])){
				}
				
			}*/
			rs = dbmd.getColumns(null, "%", TABLE_NAME[tableName_flag], "%");
			StringBuffer sb = new StringBuffer();
			while(rs.next()){
				sb.append("/**");
				sb.append("\r\n");
				sb.append("*"+rs.getString("REMARKS"));
				sb.append("\r\n");
				sb.append("*/");
				sb.append("\r\n");
				sb.append("private String "+converToField(rs.getString("COLUMN_NAME"))+";");
				sb.append("\r\n");
            }
			System.out.println(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				con.close();
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static void conver(String from,String to,Boolean reversal){
		Connection con = getConnection();
		ResultSet rs = null;
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			rs = dbmd.getColumns(null, "%", TABLE_NAME[tableName_flag], "%");
			StringBuffer sb = new StringBuffer();
			while(rs.next()){
				if(reversal){
					String field = converToField(rs.getString("COLUMN_NAME"));
					sb.append(from+".set");
					sb.append(Character.toUpperCase(field.toString().charAt(0))+field.toString().substring(1));
					sb.append("("+to+".get"+Character.toUpperCase(field.toString().charAt(0))+field.toString().substring(1)+"());");
					sb.append("\r\n");
				}else{
					String field = converToField(rs.getString("COLUMN_NAME"));
					sb.append(to+".set");
					sb.append(Character.toUpperCase(field.toString().charAt(0))+field.toString().substring(1));
					sb.append("("+from+".get"+Character.toUpperCase(field.toString().charAt(0))+field.toString().substring(1)+"());");
					sb.append("\r\n");
				}
            }
			System.out.println(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				con.close();
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private static void converToNewString(String table){
		Connection con = getConnection();
		ResultSet rs = null;
		try {
			DatabaseMetaData dbmd = con.getMetaData();
			rs = dbmd.getColumns(null, "%", TABLE_NAME[tableName_flag], "%");
			StringBuffer sb = new StringBuffer();
			while(rs.next()){
				String field = converToField(rs.getString("COLUMN_NAME"));
				sb.append("String ");
				sb.append(field);
				sb.append(" = ");
				sb.append(table);
				sb.append(".get");
				sb.append(Character.toUpperCase(field.toString().charAt(0))+field.toString().substring(1));
				sb.append("();");
				sb.append("\r\n");      
            }
			System.out.println(sb.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				con.close();
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 
	 * 方法说明：获取链接
	 *
	 * Author：        Administrator                
	 * Create Date：   2016年4月7日 上午10:09:13
	 * @return
	 *
	 */
	private static Connection getConnection() {
		Connection con = null;
		try {
			Class.forName(DRIVER_NAME[database_flag]);
			con=DriverManager.getConnection(URL[url_flag], USER[url_flag], PASSWORD[url_flag]);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	/**
	 * 
	 * 方法说明：输出制定数据库所有表信息
	 *
	 * Author：        Administrator                
	 * Create Date：   2016年4月7日 上午11:03:49
	 *
	 */
	private static void doAllTables(){
		Connection con = getConnection();
		try {
			DatabaseMetaData meta = con.getMetaData();
			ResultSet rs = meta.getTables(null, null, null, null);
			while(rs.next()){
				String tableName = rs.getString("TABLE_NAME");
				doSigleTable(tableName);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	/**
	 * 
	 * 方法说明：输入表名，输出 所有表字段
	 *
	 * Author：        Administrator                
	 * Create Date：   2016年4月7日 上午10:26:52
	 *
	 */
	private static void doSigleTable(String str){
		String tableName="";
		if("".equals(str)){
			Scanner sc = new Scanner(System.in);   
			System.out.print("输入表名:");
			tableName = sc.next();
		}else{
			tableName = str;
		}
		Connection con = getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		FileOutputStream fos = null;
		String filePath = PATH+tableName+SUFFIX;
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery("select * from "+tableName);
			ResultSetMetaData meta = rs.getMetaData();
			fos = new FileOutputStream(filePath);
			BufferedOutputStream bos = new BufferedOutputStream(fos);
			for (int i = 1; i <= meta.getColumnCount(); i++) {
				String columnName = meta.getColumnName(i).toUpperCase()+"\r\n";
				fos.write(columnName.getBytes(), 0, columnName.getBytes().length);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}catch (IOException e2) {
			e2.printStackTrace();
		}finally{
			try {
				stmt.close();
				rs.close();
				con.close();
				fos.close();
			} catch (IOException e) {
				e.printStackTrace();
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(FLAG){
			readTxtFile(filePath);
		}
		System.out.println("*******************************************************");
	}
	/**
	 * 
	 * 方法说明：读取文件并输出
	 *
	 * Author：        Administrator                
	 * Create Date：   2016年4月7日 上午11:15:22
	 * @param filePath
	 *
	 */
	public static void readTxtFile(String filePath) {
		try {
			File file = new File(filePath);
			if (file.isFile() && file.exists()) { // 判断文件是否存在
				InputStreamReader read = new InputStreamReader(
						new FileInputStream(file), ENCODING);// 考虑到编码格式
				BufferedReader bufferedReader = new BufferedReader(read);
				String lineTxt = null;
				while ((lineTxt = bufferedReader.readLine()) != null) {
					String afStr ="";
					switch (NUM) {
					case 1:
						afStr = format1(lineTxt);
						break;
					case 2:
						afStr = format2(lineTxt);
						break;
					case 3:
						afStr = format3(lineTxt);
						break;
					case 4:
						afStr = format4(lineTxt);
						break;
					}
					System.out.print(afStr);
				}
				bufferedReader.close();
				read.close();
			} else {
				System.out.println("找不到指定的文件");
			}
		} catch (Exception e) {
			System.out.println("读取文件内容出错");
			e.printStackTrace();
		}

	}
	
	public static String format1(String orignStr){
		String field = converToField(orignStr);
		return "<result column=\""+orignStr+"\" property=\""+field+"\" />\n";
	}
	
	public static String format2(String orignStr){
		String field = converToField(orignStr);
		return "#{"+field+"}";
	}
	
	public static String format3(String orignStr){
		String field = converToField(orignStr);
		return "private String "+field+";";
	}
	
	public static String format4(String orignStr){
		String field = converToField(orignStr);
		return orignStr+" = #{"+field+"}";
	}
	
	public static String converToField(String orignStr){
		char[] cs = orignStr.toCharArray();
		StringBuffer bf = new StringBuffer();
		for (int i = 0; i < cs.length; i++) {
			if(cs[i]=='_'){
				bf.append(Character.toUpperCase(cs[i+1]));
//				bf.append(cs[i+1]);
				i+=1;
			}else{
				if(Character.isDigit(cs[i])){
					bf.append(cs[i]);
				}else{
					bf.append(Character.toLowerCase(cs[i]));
//					bf.append((char)(cs[i]+32));
				}
			}
		}
		return bf.toString();
	}
}
